"""
Case Type   : 兼容M*数据库subdate函数
Case Name   : SUBDATE函数，参数1为time类型时，参数2中的时间单位为复合单位
Create At   : 2023.01.29
Owner       : z@wan005
Description :
    1、interval的时间单位为day to hour
    2、interval的时间单位为day to minute
    3、interval的时间单位为day to second
    4、interval的时间单位为hour to minute
    5、interval的时间单位为hour to second
    6、interval的时间单位为minute to second
    7、interval的时间单位为minute to second
    8、清理环境
Expect      :
    1、成功(表达式中已空格分隔为day和hour，day中的小数部分会计算到hour中并舍弃小时之后的精度)
    2、弹出告警并返回结果
    3、弹出告警并返回结果
    4、弹出告警并返回结果
    5、弹出告警并返回结果
    6、弹出告警并返回结果
    7、返回空
    8、成功
History     :modified by z@wan005，2023.10.07，select场景不报错，步骤2-步骤6，
            入参2为非法interval单位，视为'00:00:00',修改断言
            modified by zhanghuan96,2024.6.20，修改部分预期，和M保持一致
            modified by zhanghuan96，2024.06.28，优化断言
            modified by zhanghuan，2024.8.2，适配最新代码，修改断言
            modified by zhanghuan96，2024.8.26，IAHP3M问题修复，代码变更
"""

import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class CompatibilityTest019(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.common = Common()
        self.sh_primary = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.db_name = "db_subdate_0019"

    def test_subdate(self):
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.log.info('----前置条件：创建兼容b库---')
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"create database {self.db_name} dbcompatibility 'b';"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, sql_res)

        text = '--step1:interval的时间单位为day to hour;' \
               'expect:成功(表达式中已空格分隔为day和hour，day中的小数部分会计算到hour' \
               '中并舍弃小时之后的精度)--'
        self.log.info(text)
        sql_cmd = "set dolphin.support_interval_to =on;" \
                   "SELECT SUBDATE" \
                  "(time'823:59:59', INTERVAL '9 1' day to hour);" \
                  "SELECT SUBDATE" \
                  "(time'823:59:59.99999', INTERVAL '9.999 1' day to hour);" \
                  "SELECT SUBDATE" \
                  "(time'823:59:59.9', INTERVAL '9.4445 1' day to hour);" \
                  "SELECT SUBDATE" \
                  "(time'823:59:59.9', INTERVAL '9.4445 1.25' day to hour);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertTrue(len(sql_res.strip().splitlines()) >= 16, '行数不到16行')
        self.assertEqual('606:59:59',
                         sql_res.splitlines()[3].strip(), '执行失败' + text)
        self.assertEqual('',
                         sql_res.splitlines()[8].strip(), '执行失败' + text)
        self.assertEqual('',
                         sql_res.splitlines()[13].strip(), '执行失败' + text)
        self.assertEqual('',
                         sql_res.splitlines()[18].strip(), '执行失败' + text)
        self.assertEqual('',
                         sql_res.splitlines()[-2].strip(), '执行失败' + text)

        text = '-step2:interval的时间单位为day to minute;expect:弹出告警并返回结果-'
        self.log.info(text)
        sql_cmd = "set dolphin.support_interval_to =on;" \
                   "SELECT SUBDATE" \
                  "(time'23:59:59', INTERVAL '9 1' day to minute);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.assertIn('14:58:59', sql_res, '执行失败' + text)

        text = '-step3:interval的时间单位为day to second;expect:弹出告警并返回结果--'
        self.log.info(text)
        sql_cmd = "set dolphin.support_interval_to =on;" \
                   "SELECT SUBDATE" \
                  "(time'23:59:59', INTERVAL '9 59' day to second);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.assertIn('23:50:00', sql_res, '执行失败' + text)

        text = '-step4:interval的时间单位为hour to minute;expect:弹出告警并返回结果-'
        self.log.info(text)
        sql_cmd = "set dolphin.support_interval_to =on;" \
                   "SELECT SUBDATE" \
                  "(time'23:59:59', INTERVAL '9 59' day to minute);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.assertIn('14:00:59', sql_res, '执行失败' + text)

        text = '-step5:interval的时间单位为hour to second;expect:弹出告警并返回结果-'
        self.log.info(text)
        sql_cmd = "set dolphin.support_interval_to =on;" \
                   "SELECT SUBDATE" \
                  "(time'23:59:59', INTERVAL '9 59' hour to second);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.assertIn('23:50:00', sql_res, '执行失败' + text)

        text = '-step6:interval的时间单位为minute to second;' \
               'expect:弹出告警并返回结果-'
        self.log.info(text)
        sql_cmd = "set dolphin.support_interval_to =on;" \
                   "SELECT SUBDATE" \
                  "(time'23:59:59', INTERVAL '9 59' minute to second);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.assertIn('23:50:00', sql_res, '执行失败' + text)

        text = '--step7:interval的时间单位为null;expect:返回null--'
        self.log.info(text)
        sql_cmd = "SELECT SUBDATE(time'23:59:59', null);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.assertTrue(len(sql_res.strip().splitlines()) >= 4, '行数不到4行')
        self.assertEqual(0, len(sql_res.splitlines()[-2].strip()),
                         '执行失败' + text)

    def tearDown(self):
        text = '----step8:清理环境;expect:成功----'
        self.log.info(text)
        sql_cmd = f"drop database {self.db_name};"
        self.log.info(sql_cmd)
        drop_res1 = self.sh_primary.execut_db_sql(sql_cmd,
                                                  dbname='postgres')
        self.log.info(drop_res1)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, drop_res1,
                      '执行失败' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
